"""
Case Type   : 基础功能-SQL语法-DDL-partition
Case Name   : interval分区表执行exchange partition，并行跨分区merge into
Create At   : 2023/12/16
Owner       : kyeleze
Description :
    1 创建interval分区表，并插入数据
    2 第一个session开始事务A，exchange partition
    3 第二个session开始事务B，跨分区merge into
    4 每个session分别执行commit
    5 查询数据
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.查询数据修改成功
History     :
"""
import os
import unittest

import py_opengauss
from yat.test import Node

from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class IntervalPartitionCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.user_node = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.table_name1 = 't_interval_table_00011_1'
        self.table_name2 = 't_interval_table_00011_2'
        self.table_name3 = 't_interval_table_00011_3'
        self.user_param = f'-U {self.user_node.db_user} ' \
                          f'-W {self.user_node.db_password}'
        self.con_info = f'opengauss://' \
                        f'{self.user_node.db_user}:' \
                        f'{self.user_node.db_password}@' \
                        f'{self.user_node.db_host}:' \
                        f'{self.user_node.db_port}/' \
                        f'{self.user_node.db_name}'

    def test_partition_ddl_dml(self):
        text = '--step1：创建interval分区表 expect:成功--'
        self.log.info(text)
        sql_cmd = f'''exchange table if exists {self.table_name1};
                      create table {self.table_name1} (
                        prod_id number(6),
                        time_id date,
                        channel_id char(10)
                      ) partition by range (time_id) interval('1 day') (              
                        partition p1
                        values
                          less than ('2019-02-01 00:00:00'), 
                          partition p2
                        values
                          less than ('2019-02-02 00:00:00')
                      );
                      create table {self.table_name2} 
                      (prod_id number(6),
                       time_id date,
                       channel_id char(10)
                      );
                      create table {self.table_name3}
                      (prod_id NUMBER(6),
                       time_id DATE,
                       channel_id CHAR(10)
                      );'''
        result = self.primary_sh.execut_db_sql(sql_cmd,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertEqual(3, result.count(self.constant.CREATE_TABLE_SUCCESS),
                         "执行失败:" + text)
        sql_cmd = f'insert into {self.table_name1}  ' \
                  f'values(1, \'2019-01-31 00:00:00\', \'a\'), ' \
                  f'(2, \'2019-02-1 00:00:00\', \'b\');' \
                  f'insert into {self.table_name2} ' \
                  f'values(2, \'2019-02-1 00:00:00\', \'c\')'
        result = self.primary_sh.execut_db_sql(sql_cmd,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertEqual(2, result.count(self.constant.INSERT_SUCCESS_MSG),
                         "执行失败:" + text)

        text = '--step2：第一个session开始事务exchange partition expect:成功--'
        self.log.info(text)
        self.db1 = py_opengauss.open(self.con_info)
        begin_result = self.db1.query('begin;')
        self.log.info(begin_result)
        ddl_result = self.db1.query(f'alter table {self.table_name1} '
                                    f'exchange partition(p1) with table '
                                    f'{self.table_name3};')
        self.log.info(ddl_result)
        self.assertIn('BEGIN', begin_result, "执行失败:" + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG,
                      ddl_result, "执行失败:" + text)

        text = '--step3：第二个session开始事务跨分区merge into expect:成功--'
        self.log.info(text)
        self.db2 = py_opengauss.open(self.con_info)
        begin_result = self.db2.query('begin;')
        self.log.info(begin_result)
        dml_result = self.db2.query(f'merge into {self.table_name1} t '
                                    f'using {self.table_name2} t1 '
                                    f'on (t.time_id = \'2019-02-1 00:00:00\') '
                                    f'when matched then '
                                    f'update '
                                    f'set '
                                    f'  t.channel_id = t1.channel_id ' 
                                    f'where '
                                    f'  t.prod_id = 2 when not matched then'
                                    f' insert values '
                                    f'  ('
                                    f'    t1.prod_id, t1.time_id, t1.channel_id'
                                    f'  )' 
                                    f'where '
                                    f'  t1.channel_id = \'b\';')
        self.log.info(dml_result)
        self.assertIn('BEGIN', begin_result, "执行失败:" + text)
        self.assertIn('MERGE',
                      dml_result, "执行失败:" + text)

        text = '--step4：两个个session分别执行commit expect:成功--'
        self.log.info(text)
        db1_commit_result = self.db1.query('commit;')
        self.log.info(db1_commit_result)
        db2_commit_result = self.db2.query('commit;')
        self.log.info(db2_commit_result)
        self.assertIn('COMMIT', db1_commit_result, "执行失败:" + text)
        self.assertIn('COMMIT', db2_commit_result, "执行失败:" + text)

        text = '--step5：查询数据merge into成功 expect:查询数据修改成功--'
        self.log.info(text)
        sql_cmd = f'select * from {self.table_name1};'
        result = self.primary_sh.execut_db_sql(sql_cmd,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn('2 | 2019-02-01 00:00:00 | c', result, "执行失败:" + text)

    def tearDown(self):
        text = '--恢复环境，关闭session，删除测试表--'
        self.log.info(text)
        self.db1.close()
        self.db2.close()
        sql_cmd = f'drop table {self.table_name1}, {self.table_name2},' \
                  f'{self.table_name3};'
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      result, "执行失败:" + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
